﻿using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System.Data;
using System.IO;

namespace QWManage.Excel
{
    public class DataTableToExcel
    {
        private DataTable _dataTable;
        private string _fileName;

        public DataTableToExcel(DataTable dataTable, string fileName)
        {
            _dataTable = dataTable;
            _fileName = fileName;
        }
        public void CreateExcel()
        {
            XSSFWorkbook workbook = new XSSFWorkbook();
            ISheet sheet = workbook.CreateSheet("Sheet1");//创建工作表
            createCoumns(sheet);
            createRows(sheet, workbook);
            using (FileStream fs = new FileStream(_fileName, FileMode.Create, FileAccess.Write))
            {
                workbook.Write(fs);
            }
        }

        private void createCoumns(ISheet sheet)
        {
            IRow row = sheet.CreateRow(0);//在工作表中添加一行
            int cIndex = 0;
            foreach (DataColumn column in _dataTable.Columns)
            {
                ICell cell = row.CreateCell(cIndex);//在行中添加一列
                cell.SetCellValue(column.Caption);//设置列的内容	 
                sheet.SetColumnWidth(cIndex, 1 * 10 * 256);
                cIndex++;
            }
        }

        private void createRows(ISheet sheet, XSSFWorkbook workbook)
        {
            int rowIndex = 1;
            foreach (DataRow dr in _dataTable.Rows)
            {
                IRow row = sheet.CreateRow(rowIndex++);//在工作表中添加一行
                int columnIndex = 0;
                foreach (DataColumn column in _dataTable.Columns)
                {
                    string value = dr[column].ToString();
                    ICell cell = row.CreateCell(columnIndex++);//在行中添加一列
                    if (value == "") continue;
                    cell.SetCellValue(value);
                }
            }
        }
    }
}